import express from 'express'
import mysql from 'mysql2/promise'
import { dbConfig } from '../config/database.js'

const router = express.Router()

// 获取所有玩家
router.get('/', async (req, res) => {
  try {
    const connection = await mysql.createConnection(dbConfig)
    const [rows] = await connection.query(`
      SELECT 
        p.*,
        s.name as school_name,
        gl.name as grade_level_name
      FROM players p
      LEFT JOIN schools s ON p.school_id = s.id
      LEFT JOIN grade_levels gl ON p.grade_level_id = gl.id
    `)
    await connection.end()
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 创建新玩家
router.post('/', async (req, res) => {
  try {
    const { name, school_id, student_id } = req.body
    const connection = await mysql.createConnection(dbConfig)
    const [result] = await connection.query(
      'INSERT INTO players (name, school_id, student_id) VALUES (?, ?, ?)',
      [name, school_id, student_id]
    )
    await connection.end()
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

export default router 